﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MaxLeaf.Platform.Contract;
using MaxLeaf.Platform.DataContract;
using CommonsLib.DB;
using System.Transactions;
using System.Data.SqlClient;
using System.Data.Common;
using System.Diagnostics;
using CommonsLib.Settings;
using CommonsLib.Utility ;
using System.Data;

using System.Security.Cryptography;
using MaxLeaf.Platform.Functions;
using MaxLeaf.Platform.Commons;

namespace MaxLeaf.Platform.DALImplementation
{
    public class poReqHdrDAL :BaseDAL 
    {


         public bool CheckHdrExist(DBAccess dbcon , poReqHdrDataModel poReqModel)
         {
             string strSQL="Select CompanyID,FInterID from poReqHdr where CompanyID=@CompanyID";
             DbDataReader drRead = dbcon.GetDataReader(strSQL, new SqlParameter[] { new SqlParameter("@CompanyID", poReqModel.CompanyID.ToString()) });
             if (!drRead.Read())
             {
                 return false;
             }
             else
             {
                 poReqModel.Key = CvtFunc.ToInt(drRead["FInterID"]);
                 return true;
             }
         }


        public void SetPosted(DBAccess dbcon, int intHdrID)
        {
            string strSql = @"update poReqHdr set posted=1  where FInterID=@FInterID and posted=0 
                and not exists (select hdrid from poReqDtl where HdrID=@FInterID and posted=0) ";
            dbcon.ExecuteSql(strSql, new SqlParameter[] { new SqlParameter("@FInterID", intHdrID.ToString())
 
            });
        }



        internal static string CompanyTableName(object model)
        {
            string strSql = string.Format(@"
                (
                    select 0 as KeyID
                    ,H.CompanyID as CompanyID
                    ,isnull(com.Company_Name,'') as CompanyName
                    ,isnull(parentcom.CompanyID,0) as ParentCompanyId
                    ,isnull(parentcom.Company_Name,'') as ParentCompanyName

                    ,sum(d.ReqQty) as ReqQty
                    ,sum(d.Subtotal) as Subtotal
                    ,Count(distinct(d.ItemID)) as GoodsTypeCount

                    from {0}poReqHdr H
                    inner join  {0}poReqDtl D  on h.finterid=d.hdrid
                    inner join {1}mCompany com on com.CompanyID=H.CompanyID
                    left join {1}mCompany parentcom on parentcom.CompanyID=com.ParentID

                    where h.posted=0 and d.posted=0 and h.is_delete=0 and d.is_delete=0
                    group by 
                    H.CompanyID
                    ,isnull(com.Company_Name,'')
                    ,isnull(parentcom.CompanyID,0)
                    ,isnull(parentcom.Company_Name,'')
 
                ) a"
                 , GetOrdDatabase()
                 , GetBaseDatabase()

                 );

            return strSql;
        }

        internal static string ParentCompanyTableName(object model)
        {
            string strSql = string.Format(@"
                (
                    select 0 as KeyID
                    
                    ,isnull(parentcom.CompanyID,0) as ParentCompanyId
                    ,isnull(parentcom.Company_Name,'') as ParentCompanyName

                    ,sum(d.ReqQty) as ReqQty
                    ,sum(d.Subtotal) as Subtotal
                    ,Count(distinct(d.ItemID)) as GoodsTypeCount

                    from {0}poReqHdr H
                    inner join  {0}poReqDtl D  on h.finterid=d.hdrid
                    inner join {1}mCompany com on com.CompanyID=H.CompanyID
                    left join {1}mCompany parentcom on parentcom.CompanyID=com.ParentID

                    where h.posted=0 and d.posted=0 and h.is_delete=0 and d.is_delete=0
                    group by 
                    isnull(parentcom.CompanyID,0)
                    ,isnull(parentcom.Company_Name,'')
 
                ) a"
                 , GetOrdDatabase()
                 , GetBaseDatabase()

                 );

            return strSql;
        }
    }
}
